﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class ExpenseManager:ConnectionManager
    {
        public void AddExpense(ExpenseUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "INSERT INTO OEXPNSE (ExpenseCode, ExpenseName, ExpenseGLAcct) VALUES (@ExpenseCode, @ExpenseName, @ExpenseGLAcct) ";
            oCommand.Parameters.Add(new SqlParameter("@ExpenseCode", oUnit.ExpenseCode));
            oCommand.Parameters.Add(new SqlParameter("@ExpenseName", oUnit.ExpenseName));
            oCommand.Parameters.Add(new SqlParameter("@ExpenseGLAcct", oUnit.GLAccountEntry));
            
            oCommand.ExecuteNonQuery();
        }

        public void UpdateExpense(ExpenseUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE OEXPNSE SET ExpenseName=@ExpenseName, ExpenseGLAcct=@ExpenseGLAcct WHERE ExpenseCode= @ExpenseCode";
            oCommand.Parameters.Add(new SqlParameter("@ExpenseCode", oUnit.ExpenseCode));
            oCommand.Parameters.Add(new SqlParameter("@ExpenseName", oUnit.ExpenseName));
            oCommand.Parameters.Add(new SqlParameter("@ExpenseGLAcct", oUnit.GLAccountEntry));

            oCommand.ExecuteNonQuery();
        }

        public DataTable GetExpensesCodes()
        {
            DataTable dt = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();


            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OEXPNSE";

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetExpenseByCode(string sExpenseCode)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();


            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OEXPNSE WHERE ExpenseCode=@ExpenseCode";
            oCommand.Parameters.Add(new SqlParameter("@ExpenseCode", sExpenseCode));
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseExpenseByCode(string sExpenseCode)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();

            sExpenseCode = "%" + sExpenseCode + "%";
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OEXPNSE WHERE ExpenseCode LIKE @ExpenseCode";
            oCommand.Parameters.Add(new SqlParameter("@ExpenseCode", sExpenseCode));
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseExpenseByName(string sExpenseName)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();

            sExpenseName = "%" + sExpenseName + "%";
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OEXPNSE WHERE ExpenseName LIKE @ExpenseName";
            oCommand.Parameters.Add(new SqlParameter("@ExpenseName", sExpenseName));
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;
        }
    }
}
